﻿using H2.NET.Common;
using H2.NET.Web.BI.Model;
using H2.NET.Web.Workflow.DwzCode;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace H2.NET.Web.BI.Report
{
    public partial class save : BasePage
    {
        [SessionType]
        public clsCommon clsCmm = new clsCommon();
        public bi_Template template = null;
        public string cp = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            txt_cfg_group.Dialog_ID = "SYS-0003";
            txt_cfg_group.Dialog_Language = clsCmm.comm_language;
            txt_cfg_group.Dialog_Value = "c";
            txt_cfg_group.Dialog_Width = "300";
            txt_cfg_group.Dialog_Height = "360";
            txt_cfg_group.Dialog_Multiple = "1";
            int id = AksRequest.GetQueryInt("id");
            cp = AksRequest.GetQueryString("cp");
            if (id > 0)
            {
                using(var cxt = new UltimusMDC3Entities())
                {
                    template = cxt.bi_Template.Find(id);
                }
            }
            if (AksRequest.GetQueryString("action") == "save")
            {
                var context = new UltimusMDC3Entities();
                string sql = "";
                int tid = AksRequest.GetFormInt("id");
                int tempId = AksRequest.GetFormInt("tempId");
                String name = AksRequest.GetFormString("name");
                String pub = AksRequest.GetFormString("pub");
                String group = AksRequest.GetFormString("group");
                String tbls = AksRequest.GetFormString("tbls");
                String rst = AksRequest.GetFormString("rst");
                String cnRst = AksRequest.GetFormString("cnRst");
                String sum = AksRequest.GetFormString("sum");
                String count = AksRequest.GetFormString("count");
                String avg = AksRequest.GetFormString("avg");
                String search = AksRequest.GetFormString("search");
                String dwhere = AksRequest.GetFormString("where");
                String fix = AksRequest.GetFormString("fixed");
                String sort = AksRequest.GetFormString("sort").Replace("nullSort,", "").Replace(",nullSort", "").Replace("nullSort", "");
                String widths = AksRequest.GetFormString("widths");
                String ta = AksRequest.GetFormString("ta");
                List<String> searchList = search.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                List<String> sumList = sum.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                List<String> countList = count.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                List<String> avgList = avg.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                List<String> rstList = rst.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                List<String> sortList = sort.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                if (!String.IsNullOrEmpty(tbls) && !String.IsNullOrEmpty(rst))
                {
                    tbls = tbls.Substring(0, tbls.Length - 1);
                    List<String> tblList = tbls.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    String join = "";
                    if (tblList.Count() > 1)
                    {
                        foreach (var tb in tblList)
                        {
                            var ft = context.bi_Table.Where(t => t.bi_DB.name + ".dbo." + t.table_name == tb).FirstOrDefault();
                            //拼接join条件
                            if (ft != null)
                            {
                                bi_Relation relation = context.bi_Relation.Where(t => (t.bi_Table.id == ft.id && tblList.Contains(t.bi_Table1.bi_DB.name + ".dbo." + t.bi_Table1.table_name)) || (t.bi_Table1.id == ft.id && tblList.Contains(t.bi_Table.bi_DB.name + ".dbo." + t.bi_Table.table_name))).FirstOrDefault();
                                if (relation != null)
                                {
                                    String tableJoin = relation.bi_Table.bi_DB.name + ".dbo." + relation.bi_Table.table_name + "." + relation.bi_Column.column_name + "=" + relation.bi_Table1.bi_DB.name + ".dbo." + relation.bi_Table1.table_name + "." + relation.bi_Column1.column_name;
                                    if (join.IndexOf(tableJoin) < 0)
                                    {
                                        join += (String.IsNullOrEmpty(join) ? " where " : " and ") + tableJoin;
                                    }
                                }
                            }
                        }
                    }
                    if (!string.IsNullOrEmpty(dwhere))
                    {
                        join += (String.IsNullOrEmpty(join) ? " where " : " and ") + dwhere;
                    }
                    string pageHead = "select " + cnRst + " from(";
                    //string pageFooter = ") tt ";
                    //string pageFooter = ") tt where tt.Row between 1 and 20";
                    string pageRow = " ROW_NUMBER() OVER ";
                    string order = "";
                    if (!string.IsNullOrEmpty(sort))
                    {
                        order = " order by " + sort;
                    }
                    string pageOver = "(" + order + ") as Row,";
                    sql = pageHead + "select " + pageRow + pageOver + rst + " from " + tbls + (String.IsNullOrEmpty(join) ? " nowhere " : join);
                    //拼接合计
                    if (sumList.Count() > 0||countList.Count()>0||avgList.Count()>0)
                    {
                        sql += " union ";
                        string newRst = "";
                        foreach (var rs in rstList)
                        {
                            string temp = rs.Split(' ')[0];
                            string temp1 = rs.Split(' ')[1];
                            if (!String.IsNullOrEmpty(newRst))
                            {
                                newRst += ",";
                            }
                            if (sumList.Contains(temp))
                            {
                                newRst += "sum(" + temp + ") '【" + temp1.Replace("'", "") + "】合计统计'";
                            }
                            else if (countList.Contains(temp))
                            {
                                newRst += "count(" + temp + ") '【" + temp1.Replace("'", "") + "】数量统计'";
                            }
                            else if (avgList.Contains(temp))
                            {
                                newRst += "avg(" + temp + ") '【" + temp1.Replace("'", "") + "】平均统计'";
                            }
                            else
                            {
                                newRst += "null " + temp1;
                            }
                        }
                        sql += "select " + newRst + " from " + tbls + (String.IsNullOrEmpty(join) ? " nowhere " : join);
                    }   
                    //存表
                    List<string> groupList = group.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                    var dtList = new List<bi_TemplateDetail>();
                    string groupNo = "";
                    foreach (var gp in groupList)
                    {
                        var no = gp.Split(':')[0];
                        groupNo += no + ",";
                    }
                    bi_Template template = new bi_Template();
                    if (tid > 0)
                    {
                        template = context.bi_Template.Find(tid);
                        dtList = template.bi_TemplateDetail.ToList();
                        foreach (var dt in dtList)
                        {
                            context.bi_TemplateDetail.Remove(dt);
                        }
                        template.bi_TemplateDetail.Clear();
                    }
                    template.name = name;
                    template.pub = bool.Parse(pub);
                    template.permission = groupNo;
                    template.sql = sql;
                    if (tid <= 0)
                    {
                        template.create_date = DateTime.Now;
                        template.create_user = clsCmm.comm_username;
                    }
                    foreach (var rt in rstList)
                    {
                        string temp = rt.Split(' ')[0];
                        string db = temp.Split('.')[0];
                        string tbn = temp.Split('.')[2];
                        string cln = temp.Split('.')[3];
                        var column = context.bi_Column.Where(c => c.column_name == cln && c.bi_Table.table_name == tbn && c.bi_Table.bi_DB.name == db).FirstOrDefault();
                        if (column != null)
                        {
                            bi_TemplateDetail detail = new bi_TemplateDetail();
                            detail.column_id = column.id;
                            if (countList.Where(s => s == column.bi_Table.bi_DB.name + ".dbo." + column.bi_Table.table_name + "." + column.column_name).Count() > 0)
                            {
                                detail.func = 1;
                            }
                            else if (sumList.Where(s => s == column.bi_Table.bi_DB.name + ".dbo." + column.bi_Table.table_name + "." + column.column_name).Count() > 0)
                            {
                                detail.func = 2;
                            }
                            else if (avgList.Where(s => s == column.bi_Table.bi_DB.name + ".dbo." + column.bi_Table.table_name + "." + column.column_name).Count() > 0)
                            {
                                detail.func = 3;
                            }
                            else
                            {
                                detail.func = 0;
                            }
                            detail.filter = false;
                            string sortContent = sortList.Where(s => s == column.bi_Table.bi_DB.name + ".dbo." + column.bi_Table.table_name + "." + column.column_name + " asc" || s == column.bi_Table.bi_DB.name + ".dbo." + column.bi_Table.table_name + "." + column.column_name + " desc").FirstOrDefault();
                            if (!string.IsNullOrEmpty(sortContent))
                            {
                                detail.sort = sortContent.Split(' ')[1];
                            }
                            List<string> widthList = widths.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                            foreach (var wd in widthList)
                            {
                                string tempName = wd.Substring(0,wd.IndexOf("|"));
                                if (column.bi_Table.bi_DB.name + ".dbo." + column.bi_Table.table_name + "." + column.column_name == tempName)
                                {
                                    detail.width = float.Parse(wd.Substring(wd.IndexOf("|") + 1));
                                }
                            }
                            List<string> taList = ta.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                            foreach (var tl in taList)
                            {
                                string tempName = tl.Substring(0, tl.IndexOf("|"));
                                if (column.bi_Table.bi_DB.name + ".dbo." + column.bi_Table.table_name + "." + column.column_name == tempName)
                                {
                                    detail.textAlign = tl.Substring(tl.IndexOf("|") + 1);
                                }
                            }
                            detail.fix = false;
                            template.bi_TemplateDetail.Add(detail);
                        }
                    }
                    foreach (var sh in searchList)
                    {
                        var clm = context.bi_Column.Where(s => sh == (s.bi_Table.bi_DB.name + ".dbo." + s.bi_Table.table_name + "." + s.column_name)).FirstOrDefault();
                        if (clm!=null)
                        {
                            bi_TemplateDetail detail = new bi_TemplateDetail();
                            detail.column_id = clm.id;
                            detail.func = 0;
                            detail.filter = true;
                            detail.fix = false;
                            template.bi_TemplateDetail.Add(detail);
                        }
                    }
                    
                    if(!String.IsNullOrEmpty(fix)){
                        List<string> fixList = fix.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                        var oldList = dtList.Where(d=>d.fix==true && d.hide==true);
                        foreach (var f in fixList)
                        {
                            List<string> tempFix = f.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries).ToList();
                            var columnName = tempFix[0];
                            var type = tempFix[1];
                            var value = tempFix[2];
                            var column = context.bi_Column.Where(c => (c.bi_Table.bi_DB.name + ".dbo." + c.bi_Table.table_name + "." + c.column_name) == columnName).FirstOrDefault();
                            if (column != null)
                            {
                                bi_TemplateDetail detail = new bi_TemplateDetail();
                                if (tempId > 0)
                                {
                                    List<bi_TemplateDetail> oldDetails = context.bi_Template.Find(tempId).bi_TemplateDetail.ToList();
                                    if (oldDetails.Where(o => o.column_id == column.id && o.fix == true).Count() > 0)
                                    {
                                        detail.hide = true;
                                    }
                                }
                                else if (oldList.Where(o => o.column_id == column.id).Count()>0)
                                {
                                    detail.hide = true;
                                }
                                else
                                {
                                    detail.hide = false;
                                }
                                detail.column_id = column.id;
                                detail.func = 0;
                                detail.filter = false;
                                detail.fix = true;
                                detail.type = type;
                                detail.value = value;
                                template.bi_TemplateDetail.Add(detail);
                            }
                        }
                    }
                    if (tid <= 0)
                    {
                        context.bi_Template.Add(template);
                    }
                    context.SaveChanges();
                    SuccessMsg(template == null ? "" : (template.id+"|"+template.name));
                    return;
                }
            }
        }
    }
}